-- Resets the SortOrder column on all records within a given Root Hierarchy so that each is a multiple
-- of 2048.  This allows for 12 worst case inserts before a resort is needed.
CREATE PROCEDURE [dbo].[asi_HierarchyResort2] @rootHierarchyKey uniqueidentifier
AS
BEGIN
    DECLARE @NewSortOrder int
    DECLARE @RootHierarchyKeyVal uniqueidentifier

    SET NOCOUNT ON

    DECLARE @hierarchy TABLE (HierarchyKey uniqueidentifier, OldSortOrder int, NewSortOrder int IDENTITY(2048, 2048))

    INSERT INTO @hierarchy (HierarchyKey, OldSortOrder)
        SELECT HierarchyKey, SortOrder
          FROM Hierarchy
         WHERE RootHierarchyKey = @rootHierarchyKey
      ORDER BY SortOrder

    UPDATE h
       SET h.[SortOrder] = h2.[NewSortOrder]
      FROM [dbo].[Hierarchy] h INNER JOIN @hierarchy h2 ON h.[HierarchyKey] = h2.[HierarchyKey]
     WHERE h.[SortOrder] = h2.[OldSortOrder]
        

    SET NOCOUNT OFF
END
GO